package model;
import java.io.*;
import java.text.MessageFormat;
import java.util.*;
import java.sql.*;

public class database {
	private Connection con = null;
	private String jdbcDriver = "com.mysql.jdbc.Driver";
	private String dbUrl;
	private String userName;
	private String password;
	public database(String dbUrl, String userName, String password)
	{
		try {
			this.dbUrl = dbUrl;
			this.userName = userName;
			this.password = password;
			Class.forName(this.jdbcDriver);
			this.con = DriverManager.getConnection(dbUrl, userName, password);
		} catch (Exception e) {
			
		} 
	}
	public void setCon(String dbUrl, String userName, String password)
	{
		try {
			this.dbUrl = dbUrl;
			this.userName = userName;
			this.password = password;
			Class.forName(this.jdbcDriver);
			this.con = DriverManager.getConnection(dbUrl, userName, password);
		} catch (Exception e) {
			
		} 
	}
	public Connection getCon()
	{
		return this.con;
	}
	public int insert(String table, String[] keys, String[] values)
	{
		Connection con = this.con;
		String sql = MessageFormat.format("insert into {0}", table);
		String keyText = "(";
		String valueText = "(";
		PreparedStatement pstmt;
		int result = 0;
		for (int i = 0; i < keys.length; i++) 
		{
			keyText += keys[i] + ",";
		}
		keyText = keyText.substring(0, keyText.length() - 1) + ")";
		
		for (int i = 0; i < values.length; i++)
		{
			valueText += "?,";
		}
		valueText = valueText.substring(0, valueText.length() - 1) + ")";
		
		sql += keyText + "values" + valueText;
		
	
		
		try {
			pstmt = (PreparedStatement) con.prepareStatement(sql);
			for (int i = 0; i < values.length; i++)
			{
				pstmt.setString(i + 1, values[i]);
			}
			result = pstmt.executeUpdate();
			pstmt.close();
			con.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	public int update(String table, String[] keys, String[] values, String condition)
	{
		Connection con = this.con;
		PreparedStatement pstmt;
		String sql = MessageFormat.format("update {0} set ", table);
		String setText = "";
		int result = 0;
		for (int i = 0; i < keys.length; i++)
		{
			setText += MessageFormat.format(" {0} = ''{1}'',", keys[i], values[i]);
		}
		setText = setText.substring(0, setText.length() - 1) + " ";
		if (condition.equals("")){
			sql += setText;
		}else{
			sql += setText + " where " + condition;
		}
		try {
	        pstmt = (PreparedStatement) con.prepareStatement(sql);
	        result = pstmt.executeUpdate();
	        pstmt.close();
	        con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	public String[][] select(String table, String sql)
	{
		Connection con = this.con;
		PreparedStatement pstmt;
		String[][] result = null;
		String[] storeFirstRow = null;
		int rowNum = 0;
		int rowIndex = 0;
		try {
			pstmt = (PreparedStatement)con.prepareStatement(sql);
			ResultSet res = pstmt.executeQuery();
			int col = res.getMetaData().getColumnCount();
			storeFirstRow = new String[col];
			if (res.next()) {
				for (int i = 1; i <= col; i++)
				{
					storeFirstRow[i - 1] = res.getString(i);
				}
				rowIndex ++;
			}else {
				return result;
			}
			res.last();
			rowNum = res.getRow();
			result = new String[rowNum][];
			res.first();
			result[0] = storeFirstRow;
			while (res.next()) {
				
				result[rowIndex] = new String[col];
				for (int i = 1; i <= col; i++)
				{
					result[rowIndex][i - 1] = res.getString(i);
					
				}
				rowIndex ++;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
		
	}
	
	
}
